import sys
import pymysql
from datetime import datetime, timedelta
from PyQt5.QtCore import QThread, pyqtSignal
from db_connection import DatabaseConnection

class DataCleanupTask(QThread):
    """数据清除异步任务类"""
    
    progress_updated = pyqtSignal(str)
    cleanup_finished = pyqtSignal()
    error_occurred = pyqtSignal(str)
    
    def __init__(self, db_config, cleanup_option):
        super().__init__()
        self.db_config = db_config
        self.cleanup_option = cleanup_option
        
    def run(self):
        """执行数据清除任务"""
        try:
            self.progress_updated.emit("开始数据清除任务...")
            
            # 连接数据库
            connection = DatabaseConnection.create_connection(self.db_config)
            
            try:
                with connection.cursor() as cursor:
                    # 根据清除选项生成SQL条件
                    delete_sql = self.generate_delete_sql()
                    
                    if delete_sql:
                        # 先查询将要删除的记录数
                        count_sql = delete_sql.replace("DELETE FROM", "SELECT COUNT(*) FROM")
                        cursor.execute(count_sql)
                        count_result = cursor.fetchone()
                        record_count = count_result[0] if count_result else 0
                        
                        if record_count > 0:
                            self.progress_updated.emit(f"找到 {record_count} 条符合条件的记录，开始清除...")
                            
                            # 执行删除操作
                            cursor.execute(delete_sql)
                            connection.commit()
                            
                            self.progress_updated.emit(f"✅ 数据清除完成！已删除 {record_count} 条记录")
                        else:
                            self.progress_updated.emit("ℹ️ 没有找到符合条件的数据，无需清除")
                    else:
                        self.progress_updated.emit("ℹ️ 选择了不清除数据，跳过清除操作")
                        
            finally:
                connection.close()
                
            self.cleanup_finished.emit()
            
        except Exception as e:
            self.error_occurred.emit(f"数据清除过程中出错: {str(e)}")
    
    def generate_delete_sql(self):
        """根据清除选项生成对应的DELETE SQL语句"""
        if self.cleanup_option == "清除24小时之前的数据":
            # 删除24小时之前的数据
            cutoff_time = datetime.now() - timedelta(hours=24)
            return f"DELETE FROM ks_goods_rank WHERE createDate < '{cutoff_time.strftime('%Y-%m-%d %H:%M:%S')}'"
            
        elif self.cleanup_option == "清除3天之前的数据":
            # 删除3天之前的数据
            cutoff_time = datetime.now() - timedelta(days=3)
            return f"DELETE FROM ks_goods_rank WHERE createDate < '{cutoff_time.strftime('%Y-%m-%d %H:%M:%S')}'"
            
        elif self.cleanup_option == "清除1周之前的数据":
            # 删除1周之前的数据
            cutoff_time = datetime.now() - timedelta(weeks=1)
            return f"DELETE FROM ks_goods_rank WHERE createDate < '{cutoff_time.strftime('%Y-%m-%d %H:%M:%S')}'"
            
        elif self.cleanup_option == "清除所有数据":
            # 删除所有数据
            return "DELETE FROM ks_goods_rank"
            
        elif self.cleanup_option == "不清除数据":
            # 不执行清除操作
            return None
            
        else:
            return None